package com.budwk.app.util.adminUtils;

import cn.hutool.core.date.DateUtil;
import com.budwk.app.common.config.AppException;
import com.budwk.app.exception.ServiceException;
import com.budwk.app.sqfw.constants.CommonForAdminConstants;
import com.budwk.app.sqfw.dto.StatisticParamDTO;
import com.budwk.app.sqfw.dto.TreeDTO;
import com.budwk.app.sqfw.models.Sqfw_formSummary_new;
import com.budwk.app.sqfw.services.SqfwFormSummaryPdfService;
import com.budwk.app.sqfw.services.SqfwFormsummaryNewService;
import com.budwk.app.sqfw.utils.CommunityDataCleanUtils;
import com.budwk.app.sqfw.vo.SqfwDataMangeVO;
import com.budwk.app.sqfwV2.component.DayingRoadPdfComponent;
import com.budwk.app.sys.models.Sys_unit;
import com.budwk.app.sys.models.Sys_user;
import com.budwk.app.sys.services.SysDictService;
import com.budwk.app.sys.services.SysUnitService;
import com.budwk.app.sys.services.SysUserService;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.common.constant.RedisConstant;
import com.budwk.starter.common.result.ResultCode;
import com.budwk.starter.security.utils.SecurityUtil;
import lombok.extern.slf4j.Slf4j;
import net.sourceforge.pinyin4j.PinyinHelper;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.ioc.impl.PropertiesProxy;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @program: dfxy-community
 * @description: 公共校验工具类（后台管理系统）
 * @author: LJT
 * @create: 2024-02-18 16:28
 **/
@Slf4j
public class CommonCheckUtils {

    public static final String ZHISHU = "直属";// 直属

    public final static String TO_DO_CHECK_OUT = "1";// 对于后台：表示未检查，对于前台：表示超时

    public final static String CHECK_OUT = "2";// 对于后台：表示已检查，对于前台：表示未超时

    public static final SimpleDateFormat SDF_START = new SimpleDateFormat("yyyy-MM-dd 00:00:00");// 开始统计时间格式
    public static final SimpleDateFormat SDF_END = new SimpleDateFormat("yyyy-MM-dd 23:59:59");// 结束统计时间格式

    private static final String TIME_TYPE_YESTERDAY = "0";// 昨日
    private static final String TIME_TYPE_NOW_DAY = "1";// 今日
    private static final String TIME_TYPE_NOW_WEEK = "2";// 本周
    private static final String TIME_TYPE_NOW_MONTH = "3";// 近一个月

    /**
     * 检查行政区域5级
     *
     * @param criSql
     * @return
     */
    public static void checkAreaRange(SqlExpressionGroup criSql) {
        // 区域5级
        SysUserService sysUserService = NutzIocUtils.getBean(SysUserService.class);
        Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
        if (ObjectUtils.isEmpty(sysUser)) {
            throw new AppException(ResultCode.USER_NOT_FOUND.getMsg());
        }
        // 省
        if (StringUtils.isNotBlank(sysUser.getProvince()) && !ZHISHU.equals(sysUser.getProvince())) {
            criSql.andEquals(Sys_user::getProvince, sysUser.getProvince());
        }
        // 市
        if (StringUtils.isNotBlank(sysUser.getCity()) && !ZHISHU.equals(sysUser.getCity())) {
            criSql.andEquals(Sys_user::getCity, sysUser.getCity());
        }
        // 区
        if (StringUtils.isNotBlank(sysUser.getDistrict()) && !ZHISHU.equals(sysUser.getDistrict())) {
            criSql.andEquals(Sys_user::getDistrict, sysUser.getDistrict());
        }
        // 街道
        if (StringUtils.isNotBlank(sysUser.getCounty()) && !ZHISHU.equals(sysUser.getCounty())) {
            criSql.andEquals(Sys_user::getCounty, sysUser.getCounty());
        }
        // 辖区
        if (StringUtils.isNotBlank(sysUser.getCommunity()) && !ZHISHU.equals(sysUser.getCommunity())) {
            criSql.andEquals(Sys_user::getCommunity, sysUser.getCommunity());
        }
    }

    /**
     * 检查行政区域5级
     *
     * @param cnd
     * @return
     */
    public static void checkAreaRangeCnd(Cnd cnd) {
        // 区域5级
        SysUserService sysUserService = NutzIocUtils.getBean(SysUserService.class);
        Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
        if (ObjectUtils.isEmpty(sysUser)) {
            throw new AppException(ResultCode.USER_NOT_FOUND.getMsg());
        }
        // 省
        if (StringUtils.isNotBlank(sysUser.getProvince()) && !ZHISHU.equals(sysUser.getProvince())) {
            cnd.and(Sys_user::getProvince, "=", sysUser.getProvince());
        }
        // 市
        if (StringUtils.isNotBlank(sysUser.getCity()) && !ZHISHU.equals(sysUser.getCity())) {
            cnd.and(Sys_user::getCity, "=", sysUser.getCity());
        }
        // 区
        if (StringUtils.isNotBlank(sysUser.getDistrict()) && !ZHISHU.equals(sysUser.getDistrict())) {
            cnd.and(Sys_user::getDistrict, "=", sysUser.getDistrict());
        }
        // 街道
        if (StringUtils.isNotBlank(sysUser.getCounty()) && !ZHISHU.equals(sysUser.getCounty())) {
            cnd.and(Sys_user::getCounty, "=", sysUser.getCounty());
        }
        // 辖区
        if (StringUtils.isNotBlank(sysUser.getCommunity()) && !ZHISHU.equals(sysUser.getCommunity())) {
            cnd.and(Sys_user::getCommunity, "=", sysUser.getCommunity());
        }
    }

    /**
     * 检查行政区域5级
     *
     * @return
     */
    public static String getLv5() {
        StringBuilder stringBuilder = new StringBuilder();
        // 区域5级
        SysUserService sysUserService = NutzIocUtils.getBean(SysUserService.class);
        Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
        if (ObjectUtils.isEmpty(sysUser)) {
            throw new AppException(ResultCode.USER_NOT_FOUND.getMsg());
        }
        // 省
        if (StringUtils.isNotBlank(sysUser.getProvince()) && !ZHISHU.equals(sysUser.getProvince())) {
            stringBuilder.append(" and province = '").append(sysUser.getProvince()).append("'");
        }
        // 市
        if (StringUtils.isNotBlank(sysUser.getCity()) && !ZHISHU.equals(sysUser.getCity())) {
            stringBuilder.append(" and city = '").append(sysUser.getCity()).append("'");
        }
        // 区
        if (StringUtils.isNotBlank(sysUser.getDistrict()) && !ZHISHU.equals(sysUser.getDistrict())) {
            stringBuilder.append(" and district = '").append(sysUser.getDistrict()).append("'");
        }
        // 街道
        if (StringUtils.isNotBlank(sysUser.getCounty()) && !ZHISHU.equals(sysUser.getCounty())) {
            stringBuilder.append(" and county = '").append(sysUser.getCounty()).append("'");
        }
        // 辖区
        if (StringUtils.isNotBlank(sysUser.getCommunity()) && !ZHISHU.equals(sysUser.getCommunity())) {
            stringBuilder.append(" and community = '").append(sysUser.getCommunity()).append("'");
        }
        return stringBuilder.toString();
    }

    /**
     * 获取pdf路径
     */
    public static Map<String, String> getPdfUrlByFlowId(List<Sqfw_formSummary_new> sqfwCustomForAdminCommonVOList) {
        if (ObjectUtils.isEmpty(sqfwCustomForAdminCommonVOList)) {
            return null;
        }
        SqfwFormsummaryNewService sqfwFormsummaryNewService = NutzIocUtils.getBean(SqfwFormsummaryNewService.class);
        Set<String> flowIdSet = sqfwCustomForAdminCommonVOList.stream().map(Sqfw_formSummary_new::getSqfwFlowId).collect(Collectors.toSet());
        StringBuffer sbIds = new StringBuffer();
        for (String flowId : flowIdSet) {
            sbIds.append("'").append(flowId).append("',");
        }
        if (sbIds.length() <= 0) {
            return null;
        }
        String substring = sbIds.substring(0, sbIds.length() - 1);
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT id, resource_path FROM shequ.\"sqfw_completeflow\"  WHERE 1 = 1 ");
        if (StringUtils.isNotEmpty(substring)) {
            sbSql.append(" AND id IN (").append(substring).append(")");
        }
        Sql sql = Sqls.create(sbSql.toString());
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                Map<String, String> pdfUrlMap = new HashMap<String, String>();
                while (rs.next()) {
                    String id = "";
                    for (int i = 0; i < columnCount; i++) {
                        String resourcePath = rs.getString("resource_path");
                        if (StringUtils.isNotEmpty(resourcePath) && resourcePath.startsWith("/")) {
                            String flowId = rs.getString("id");
                            pdfUrlMap.put(flowId, resourcePath);
                        }
                    }
                }
                return pdfUrlMap;
            }
        });
        sqfwFormsummaryNewService.execute(sql);
        return sql.getObject(Map.class);
    }

    /**
     * 查询数据字典，没有查询到内容则返回传入参数
     */
    public static String queryDataDict(String paramStr) {
        String result = paramStr;
        if (org.apache.commons.lang3.StringUtils.isEmpty(paramStr)) {
            return result;
        }
        try {
            result = DayingRoadPdfComponent.getDictName(paramStr, null);
        } catch (Exception ex) {
            log.info("queryDataDict-查询数据字典异常：{}", ex.getMessage());
        }
        return result;
    }

    /**
     * 查询下载文档列表信息
     *
     * @return
     */
    public static LinkedHashMap<String, String> getDownloadFileInfo(String code) {
        if (StringUtils.isEmpty(code)) {
            return new LinkedHashMap<>();
        }
        SysDictService sysDictService = NutzIocUtils.getBean(SysDictService.class);
        Sql sql = Sqls.create("SELECT code, name FROM shequ.\"sys_dict\" WHERE parentid IN (SELECT id FROM shequ.\"sys_dict\" WHERE code = @code) AND disabled = FALSE ORDER BY createdat ASC");
        sql.setParam("code", code);
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                Map<String, String> sysDictMap = new LinkedHashMap<String, String>();
                while (rs.next()) {
                    String code = rs.getString("code");
                    String name = rs.getString("name");
                    sysDictMap.put(code, name);
                }
                return sysDictMap;
            }
        });
        sysDictService.execute(sql);
        return sql.getObject(LinkedHashMap.class);
    }

    /**
     * 查询下载文档列表信息
     *
     * @return
     */
    public static LinkedHashMap<String, String> getDictInfoMap(String code) {
        if (StringUtils.isEmpty(code)) {
            return new LinkedHashMap<>();
        }
        SysDictService sysDictService = NutzIocUtils.getBean(SysDictService.class);
        Sql sql = Sqls.create("SELECT code, name FROM shequ.\"sys_dict\" WHERE parentid IN (SELECT id FROM shequ.\"sys_dict\" WHERE code = @code) AND disabled = FALSE ORDER BY updatedat ASC");
        sql.setParam("code", code);
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                Map<String, String> sysDictMap = new LinkedHashMap<String, String>();
                while (rs.next()) {
                    String code = rs.getString("code");
                    String name = rs.getString("name");
                    sysDictMap.put(code, name);
                }
                return sysDictMap;
            }
        });
        sysDictService.execute(sql);
        return sql.getObject(LinkedHashMap.class);
    }

    /**
     * 查询下载文档列表信息
     *
     * @return
     */
    public static Map<String, String> getDictInfoMaps(List<String> codes) {
        if (ObjectUtils.isEmpty(codes) || codes.size() <= 0) {
            return new LinkedHashMap<>();
        }
        SysDictService sysDictService = NutzIocUtils.getBean(SysDictService.class);
        Sql sql = Sqls.create("SELECT code, name FROM shequ.\"sys_dict\" WHERE parentid IN (SELECT id FROM shequ.\"sys_dict\" WHERE code IN (@codes)) AND disabled = FALSE ");
        sql.setParam("codes", codes);
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                Map<String, String> sysDictMap = new LinkedHashMap<String, String>();
                while (rs.next()) {
                    String code = rs.getString("code");
                    String name = rs.getString("name");
                    sysDictMap.put(code, name);
                }
                return sysDictMap;
            }
        });
        sysDictService.execute(sql);
        return sql.getObject(LinkedHashMap.class);
    }

    /**
     * 查询下载文档列表信息
     *
     * @return
     */
    public static Map<String, String> getDictSubInfoMaps(List<String> codes) {
        if (ObjectUtils.isEmpty(codes) || codes.size() <= 0) {
            return new LinkedHashMap<>();
        }
        SysDictService sysDictService = NutzIocUtils.getBean(SysDictService.class);
        Sql sql = Sqls.create("SELECT code, name FROM shequ.\"sys_dict\"  WHERE code IN (@codes) AND disabled = FALSE ");
        sql.setParam("codes", codes);
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                Map<String, String> sysDictMap = new LinkedHashMap<String, String>();
                while (rs.next()) {
                    String code = rs.getString("code");
                    String name = rs.getString("name");
                    sysDictMap.put(code, name);
                }
                return sysDictMap;
            }
        });
        sysDictService.execute(sql);
        return sql.getObject(LinkedHashMap.class);
    }

    /**
     * 获取首字符拼音
     *
     * @param str
     * @return
     */
    public static String getPinyinFirstLetter(String str) {
        if (str == null || str.trim().isEmpty()) {
            return str;
        }
        char firstChar = str.charAt(0);
        String[] pinyinArray = PinyinHelper.toHanyuPinyinStringArray(firstChar);
        if (pinyinArray != null && pinyinArray.length > 0) {
            return pinyinArray[0].charAt(0) + "";
        }
        return str;
    }

    /**
     * 根据类型计算时间区间
     *
     * @param timeType
     * @return StatisticParamDTO
     */
    public static StatisticParamDTO statisticTimeSectionByType(String timeType) {
        StatisticParamDTO statisticParamDTO = new StatisticParamDTO();
        if (TIME_TYPE_YESTERDAY.equals(timeType)) {
            // 昨日开始
            statisticParamDTO.setStartTime(DateUtil.parse(SDF_START.format(DateUtil.yesterday())));
            // 昨日结束
            statisticParamDTO.setEndTime(DateUtil.parse(SDF_END.format(DateUtil.yesterday())));
            // 前日开始
            statisticParamDTO.setLastStartTime(DateUtil.offsetDay(DateUtil.parse(SDF_START.format(DateUtil.yesterday())), -1));
            // 前日结束
            statisticParamDTO.setLastEndTime(DateUtil.offsetDay(DateUtil.parse(SDF_END.format(DateUtil.yesterday())), -1));
        } else if (TIME_TYPE_NOW_DAY.equals(timeType)) {
            // 今日开始
            statisticParamDTO.setStartTime(DateUtil.parse(SDF_START.format(statisticParamDTO.getNowTime())));
            // 今日结束
            statisticParamDTO.setEndTime(statisticParamDTO.getNowTime());
            // 昨日开始
            statisticParamDTO.setLastStartTime(DateUtil.parse(SDF_START.format(DateUtil.yesterday())));
            // 昨日结束
            statisticParamDTO.setLastEndTime(DateUtil.parse(SDF_END.format(DateUtil.yesterday())));
        } else if (TIME_TYPE_NOW_WEEK.equals(timeType)) {
            // 本周第一天00:00:00
            statisticParamDTO.setStartTime(DateUtil.parse(SDF_START.format(DateUtil.beginOfWeek(statisticParamDTO.getNowTime()))));
            // 本周当天时间
            statisticParamDTO.setEndTime(statisticParamDTO.getNowTime());
            // 上周第一天00:00:00
            statisticParamDTO.setLastStartTime(DateUtil.parse(SDF_START.format(DateUtil.offsetWeek(statisticParamDTO.getStartTime(), -1))));
            // 上周最后一天24:00:00
            statisticParamDTO.setLastEndTime(DateUtil.parse(SDF_END.format(DateUtil.endOfWeek(statisticParamDTO.getLastStartTime()))));
        } else if (TIME_TYPE_NOW_MONTH.equals(timeType)) {
            // 近一个月第一天00:00:00
            statisticParamDTO.setStartTime(DateUtil.parse(SDF_START.format(DateUtil.offsetMonth(statisticParamDTO.getNowTime(), -1))));
            // 近一个月当日时间
            statisticParamDTO.setEndTime(statisticParamDTO.getNowTime());
            // 近一个月第一天00:00:00
            statisticParamDTO.setLastStartTime(DateUtil.parse(SDF_START.format(DateUtil.offsetMonth(statisticParamDTO.getStartTime(), -1))));
            // 近一个月最后一天24:00:00
            statisticParamDTO.setLastEndTime(DateUtil.parse(SDF_END.format(DateUtil.offsetDay(statisticParamDTO.getStartTime(), -1))));
        }
        return statisticParamDTO;
    }

    /**
     * 检查用户对应角色对应的数据权限
     *
     * @param criSql
     * @return
     */
    public static void checkUserDataAuth(SqlExpressionGroup criSql) {
        // 1. 查询用户关联的角色数据，获取最大的权限
        Dao dao = (Dao) NutzIocUtils.getBeanByType(Dao.class);
        Sql sql = Sqls.create("SELECT MAX(CAST(datalevel as int)) FROM shequ.\"sys_role\" sm WHERE id IN (SELECT roleid  FROM shequ.\"sys_role_user\"  WHERE userid = @userid)");
        sql.setParam("userid", SecurityUtil.getUserId());
        sql.setCallback(Sqls.callback.integer());
        dao.execute(sql);
        int dataLevel = sql.getInt();
        if (ObjectUtils.isEmpty(dataLevel)) {
            throw new AppException("未配置数据权限，请确认！");
        }
        SysUserService sysUserService = (SysUserService) NutzIocUtils.getBeanByType(SysUserService.class);
        Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
        if (ObjectUtils.isEmpty(sysUser)) {
            throw new AppException("用户不存在，请确认！");
        }
        /**
         *  1. 可选【全部数据权限】（所有数据均可查看）
         *  2. 【本区域及以下数据权限】（只和区划关联，不和部门关联）
         *  3. 【本部门及以下部门数据权限】（只和部门关联，不和区域关联）
         *  4. 【本部门数据权限】
         *  5. 【仅本人数据权限】（只能查看自己产生的数据）
         */
        if (1 == dataLevel) {
            return;
        } else if (2 == dataLevel) {
            checkAreaRange(criSql);
        } else if (3 == dataLevel) {
            Sql unitSql = Sqls.create("SELECT id FROM sys_unit WHERE id = @unitid OR parentid = @unitid");
            unitSql.setParam("unitid", sysUser.getUnitId());
            unitSql.setCallback(Sqls.callback.integer());
            dao.execute(unitSql);
            List<String> unitIds = unitSql.getList(String.class);
            if (ObjectUtils.isEmpty(unitIds)) {
                throw new AppException("未查询到单位信息，请确认！");
            }
            criSql.andInStrList(Sqfw_formSummary_new::getUnitId, unitIds);
        } else if (4 == dataLevel) {
            criSql.andEquals(Sqfw_formSummary_new::getUnitId, sysUser.getUnitId());
        } else if (5 == dataLevel) {
            criSql.andEquals(Sqfw_formSummary_new::getCreateBy, sysUser.getId());
        }
    }

    /**
     * 检查行政区域5级
     *
     * @return
     */
    public static String checkAreaRangeStr(Sqfw_formSummary_new sqfwFormSummaryNew) {
        // 区域5级
        SysUserService sysUserService = NutzIocUtils.getBean(SysUserService.class);
        Sys_user sysUser = sysUserService.fetch("8e95c8ee7e10440eadc9e5d60b89adf5");
        if (ObjectUtils.isEmpty(sysUser)) {
            throw new AppException(ResultCode.USER_NOT_FOUND.getMsg());
        }
        StringBuffer sb = new StringBuffer();
        // =================================检查登录用户========================================
        // 省
        if (StringUtils.isNotBlank(sysUser.getProvince()) && !ZHISHU.equals(sysUser.getProvince())) {
            sb.append(" AND province = '").append(sysUser.getProvince()).append("' ");
        }
        // 市
        if (StringUtils.isNotBlank(sysUser.getCity()) && !ZHISHU.equals(sysUser.getCity())) {
            sb.append(" AND city = '").append(sysUser.getCity()).append("' ");
        }
        // 区
        if (StringUtils.isNotBlank(sysUser.getDistrict()) && !ZHISHU.equals(sysUser.getDistrict())) {
            sb.append(" AND district = '").append(sysUser.getDistrict()).append("' ");
        }
        // 街道
        if (StringUtils.isNotBlank(sysUser.getCounty()) && !ZHISHU.equals(sysUser.getCounty())) {
            sb.append(" AND county = '").append(sysUser.getDistrict()).append("' ");
        }
        // 辖区
        if (StringUtils.isNotBlank(sysUser.getCommunity()) && !ZHISHU.equals(sysUser.getCommunity())) {
            sb.append(" AND community = '").append(sysUser.getDistrict()).append("' ");
        }
        // =================================检查输入参数========================================
        if (ObjectUtils.isEmpty(sqfwFormSummaryNew)) {
            return sb.toString();
        }
        // 省
        if (StringUtils.isNotBlank(sqfwFormSummaryNew.getProvince()) && !ZHISHU.equals(sqfwFormSummaryNew.getProvince())) {
            sb.append(" AND province = '").append(sqfwFormSummaryNew.getProvince()).append("' ");
        }
        // 市
        if (StringUtils.isNotBlank(sqfwFormSummaryNew.getCity()) && !ZHISHU.equals(sqfwFormSummaryNew.getCity())) {
            sb.append(" AND city = '").append(sqfwFormSummaryNew.getCity()).append("' ");
        }
        // 区
        if (StringUtils.isNotBlank(sqfwFormSummaryNew.getDistrict()) && !ZHISHU.equals(sqfwFormSummaryNew.getDistrict())) {
            sb.append(" AND district = '").append(sqfwFormSummaryNew.getDistrict()).append("' ");
        }
        // 街道
        if (StringUtils.isNotBlank(sqfwFormSummaryNew.getCounty()) && !ZHISHU.equals(sqfwFormSummaryNew.getCounty())) {
            sb.append(" AND county = '").append(sqfwFormSummaryNew.getDistrict()).append("' ");
        }
        // 辖区
        if (StringUtils.isNotBlank(sqfwFormSummaryNew.getCommunity()) && !ZHISHU.equals(sqfwFormSummaryNew.getCommunity())) {
            sb.append(" AND community = '").append(sqfwFormSummaryNew.getDistrict()).append("' ");
        }
        return sb.toString();
    }

    /**
     * 检查当前登录用户的行政区域级别
     *
     * @return
     */
    public static String checkUserArea() {
        SysUserService sysUserService = NutzIocUtils.getBean(SysUserService.class);
        Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
        if (StringUtils.isEmpty(sysUser.getProvince()) || CommonForAdminConstants.ZHISHU.equals(sysUser.getProvince())) {
            return CommonForAdminConstants.PROVINCE;
        } else if (StringUtils.isEmpty(sysUser.getCity()) || CommonForAdminConstants.ZHISHU.equals(sysUser.getCity())) {
            return CommonForAdminConstants.PROVINCE;
        } else if (StringUtils.isEmpty(sysUser.getDistrict()) || CommonForAdminConstants.ZHISHU.equals(sysUser.getDistrict())) {
            return CommonForAdminConstants.CITY;
        } else if (StringUtils.isEmpty(sysUser.getCounty()) || CommonForAdminConstants.ZHISHU.equals(sysUser.getCounty())) {
            return CommonForAdminConstants.DISTRICT;
        } else if (StringUtils.isEmpty(sysUser.getCommunity()) || CommonForAdminConstants.ZHISHU.equals(sysUser.getCommunity())) {
            return CommonForAdminConstants.COUNTY;
        }
        return "";
    }

    /**
     * 检查字符串是否是中文
     *
     * @param str
     * @return
     */
    public static boolean containsChinese(String str) {
        String regex = "[\\u4e00-\\u9fa5]";
        return str.matches(".*" + regex + ".*");
    }


    // -----------------------获取用户所属区域开始

    /**
     * 获取用户所属区域
     *
     * @return
     */
    public static TreeDTO getRegion() {
        Sys_user sysUser = NutzIocUtils.getBean(SysUserService.class).getUserById(SecurityUtil.getUserId());

        Sys_unit sysUnit = NutzIocUtils.getBean(SysUnitService.class).fetch(sysUser.getUnitId());

        return getName(sysUnit);
    }

    /**
     * 获取子单位
     *
     * @param id
     * @return
     */
    public static List<TreeDTO> getChild(String id) {
        Cnd cnd = Cnd.NEW();
        cnd.and(Sys_unit::getParentId, "=", id);
        List<Sys_unit> unitList = NutzIocUtils.getBean(SysUnitService.class).query(cnd);
        List<TreeDTO> treeDTOList = new ArrayList<>();
        for (Sys_unit unit : unitList) {
            treeDTOList.add(getName(unit));
        }
        return treeDTOList;
    }

    /**
     * 检测是否存在下级节点
     *
     * @param id
     * @return
     */
    private static boolean hasChild(String id) {
        Cnd cnd = Cnd.NEW();
        cnd.and(Sys_unit::getParentId, "=", id);
        Sys_unit unit = NutzIocUtils.getBean(SysUnitService.class).fetch(cnd);
        return unit != null ? true : false;
    }

    private static TreeDTO getName(Sys_unit unit) {
        // 市
        if (org.apache.commons.lang.StringUtils.isBlank(unit.getCity()) || ZHISHU.equals(unit.getCity())) {
            return new TreeDTO(unit.getId(), unit.getProvince(), TreeDTO.P, hasChild(unit.getId()));
        }
        // 区
        if (org.apache.commons.lang.StringUtils.isBlank(unit.getDistrict()) || ZHISHU.equals(unit.getDistrict())) {
            return new TreeDTO(unit.getId(), unit.getCity(), TreeDTO.C, hasChild(unit.getId()));
        }
        // 街道
        if (org.apache.commons.lang.StringUtils.isBlank(unit.getCounty()) || ZHISHU.equals(unit.getCounty())) {
            return new TreeDTO(unit.getId(), unit.getDistrict(), TreeDTO.D, hasChild(unit.getId()));
        }
        // 辖区
        if (org.apache.commons.lang.StringUtils.isBlank(unit.getCommunity()) || ZHISHU.equals(unit.getCommunity())) {
            return new TreeDTO(unit.getId(), unit.getCounty(), TreeDTO.J, hasChild(unit.getId()));
        }
        // 辖区
        if (org.apache.commons.lang.StringUtils.isNotBlank(unit.getCommunity()) && !ZHISHU.equals(unit.getCommunity())) {
            return new TreeDTO(unit.getId(), unit.getCommunity(), TreeDTO.S, hasChild(unit.getId()));
        }

        return null;
    }

    /**
     * 设置五级
     *
     * @param criSql
     * @param unitId 前端传来的 五级区域id
     */
    public static void setCriSql(SqlExpressionGroup criSql, String unitId) {
        String sql = "WITH RECURSIVE dict AS (" +
                "     SELECT *" +
                "     FROM shequ.sys_unit" +
                "     WHERE id = '" + unitId + "'" +
                "     union ALL" +
                "     SELECT shequ.sys_unit.*" +
                "     FROM shequ.sys_unit," +
                "          dict" +
                "     WHERE shequ.sys_unit.id = dict.parentid" +
                ")" +
                "SELECT * FROM dict";
        List<Sys_unit> unitList = NutzIocUtils.getBean(SysUnitService.class).listEntity(Sqls.create(sql));

        // LinkedHashSet能保证元素添加的顺序
        Set<String> set = new LinkedHashSet<>();
        for (Sys_unit unit : unitList) {
            if (org.apache.commons.lang.StringUtils.isNotBlank(unit.getProvince()) && !ZHISHU.equals(unit.getProvince())) {
                set.add(unit.getProvince());
            }
        }
        for (Sys_unit unit : unitList) {
            if (org.apache.commons.lang.StringUtils.isNotBlank(unit.getCity()) && !ZHISHU.equals(unit.getCity())) {
                set.add(unit.getCity());
            }
        }
        for (Sys_unit unit : unitList) {
            if (org.apache.commons.lang.StringUtils.isNotBlank(unit.getDistrict()) && !ZHISHU.equals(unit.getDistrict())) {
                set.add(unit.getDistrict());
            }
        }
        for (Sys_unit unit : unitList) {
            if (org.apache.commons.lang.StringUtils.isNotBlank(unit.getCounty()) && !ZHISHU.equals(unit.getCounty())) {
                set.add(unit.getCounty());
            }
        }
        for (Sys_unit unit : unitList) {
            if (org.apache.commons.lang.StringUtils.isNotBlank(unit.getCommunity()) && !ZHISHU.equals(unit.getCommunity())) {
                set.add(unit.getCommunity());
            }
        }

        if (set.size() == 0) throw new ServiceException("数据不存在");

        int i = 0;
        for (String name : set) {
            setSql(criSql, i, name);
            i++;
        }
    }

    private static void setSql(SqlExpressionGroup criSql, int i, String name) {
        switch (i) {
            case 0:
                criSql.andEquals(Sys_user::getProvince, name);
                break;
        }
        switch (i) {
            case 1:
                criSql.andEquals(Sys_user::getCity, name);
                break;
        }
        switch (i) {
            case 2:
                criSql.andEquals(Sys_user::getDistrict, name);
                break;
        }
        switch (i) {
            case 3:
                criSql.andEquals(Sys_user::getCounty, name);
                break;
        }
        switch (i) {
            case 4:
                criSql.andEquals(Sys_user::getCommunity, name);
                break;
        }
    }

    // -----------------------获取用户所属区域结束

    /**
     * 获取pdf路径，从shequ.sqfw_form_summary_pdf中取
     */
    public static Map<String, String> getPdfUrlBySummaryId(List<Sqfw_formSummary_new> sqfwCustomForAdminCommonVOList, List<Integer> indexs) {
        if (ObjectUtils.isEmpty(sqfwCustomForAdminCommonVOList)) {
            return null;
        }
        SqfwFormSummaryPdfService sqfwFormSummaryPdfService = NutzIocUtils.getBean(SqfwFormSummaryPdfService.class);
        Set<String> summaryIds = sqfwCustomForAdminCommonVOList.stream().map(Sqfw_formSummary_new::getId).collect(Collectors.toSet());
        StringBuffer sbIds = new StringBuffer();
        for (String summaryId : summaryIds) {
            sbIds.append("'").append(summaryId).append("',");
        }
        if (sbIds.length() <= 0) {
            return null;
        }

        StringBuffer sbIndexs = new StringBuffer();
        for (Integer index : indexs) {
            sbIndexs.append(index).append(",");
        }
        if (sbIndexs.length() <= 0) {
            return null;
        }
        PropertiesProxy propertiesProxy = NutzIocUtils.getBean(PropertiesProxy.class);
        String url = propertiesProxy.get("huawei-oss.url");

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT summary_id, index, url FROM (SELECT row_number() OVER (PARTITION BY summary_id, index ORDER BY create_date DESC) rowid, FROM shequ.\"sqfw_form_summary_pdf\" WHERE 1 = 1 ");
        sbSql.append(" AND summary_id IN (").append(sbIds.substring(0, sbIds.length() - 1)).append(")");
        sbSql.append(" AND index IN (").append(sbIndexs.substring(0, sbIds.length() - 1)).append(")");
        sbSql.append(" ) temp where rowid = 1");
        Sql sql = Sqls.create(sbSql.toString());
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                Map<String, String> pdfUrlMap = new HashMap<String, String>();
                while (rs.next()) {
                    String id = "";
                    for (int i = 0; i < columnCount; i++) {
                        String url = rs.getString("url");
                        if (StringUtils.isEmpty(url)) {
                            continue;
                        }
                        url = url.substring(url.length());
                        if (url.startsWith("/")) {
                            String summaryId = rs.getString("summary_id");
                            String index = rs.getString("index");
                            pdfUrlMap.put(summaryId + index, url);
                        }
                    }
                }
                return pdfUrlMap;
            }
        });
        sqfwFormSummaryPdfService.execute(sql);
        return sql.getObject(Map.class);
    }

    /**
     * 分页查询
     * 生成redisKey
     */
    public static String generateCacheKey(SqfwDataMangeVO bean) {
        String redisKey = "";
        SysUserService sysUserService = NutzIocUtils.getBean(SysUserService.class);
        Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
        StringBuffer sb = new StringBuffer();
        sb.append(sysUser.getProvince())
                .append(sysUser.getCity())
                .append(sysUser.getDistrict())
                .append(sysUser.getCounty())
                .append(sysUser.getCommunity())
                .append(CommunityDataCleanUtils.getFieldValuesConcatenated(bean));
        String mainKey = CommunityDataCleanUtils.StringToNumbers(sb.toString());
        redisKey = RedisConstant.WKCACHE + "datamanage:" + mainKey + "_" + bean.getPageNo() + "_" + bean.getPageSize();
        log.info("redisKey: {}", redisKey);
        return redisKey;
    }
}
